import logging
from datetime import datetime
from typing import List, Optional

from fastapi import APIRouter, Query, Depends, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

scenario_router = APIRouter(prefix="/scene/scenario", tags=["指令&控制管理"])


class SceneSearch(PaginationBase):
    fsiteNo: str = Field(None, description="站点编号")
    fscenetype: str = Field(None, description="")


# todo 13.定时场景设置
@scenario_router.get("/list", summary="查询定时控制")
def list_scene(scene: SceneSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from s_tc_scene_head"
    sql = sql + " where 1=1"
    if scene.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    if scene.fscenetype is not None:
        sql = sql + " and fscenetype= :fscenetype "
        param["fscenetype"] = scene.fscenetype
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fairitem,fexectime,fexecweek1,fexecweek2,fexecweek3,fexecweek4"
    sql = sql + ",fexecweek5,fexecweek6,fexecweek7,foperator,frange"
    sql = sql + ",fscenename,fsceneno,fscenetype,fsite_no,fteacherno"
    sql = sql + ",fupddate,fvalue,fvalue2,fwhere,fwhere2"
    sql = sql + " from s_tc_scene_head"
    sql = sql + " where 1=1"
    if scene.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    if scene.fscenetype is not None:
        sql = sql + " and fscenetype= :fscenetype "
        param["fscenetype"] = scene.fscenetype
    sql = sql + " limit :limit offset :offset "
    param["offset"] = scene.offset
    param["limit"] = scene.limit
    data = db.execute(text(sql), param).fetchall()

    # atsPoints 明细列表
    param = {}
    sql_3 = " select b.fentryid,b.fsceneno,b.fpoint_no fpointno "
    sql_3 = sql_3 + " ,c.fpoint_name fpointName"
    sql_3 = sql_3 + " from s_tc_scene_head a"
    sql_3 = sql_3 + " inner join s_tc_scene_point b on a.fsceneno=b.fsceneno"
    sql_3 = sql_3 + " left join a_ts_point c on b.fpoint_no=c.fpoint_no"
    sql_3 = sql_3 + " where 1=1 "
    if scene.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    if scene.fscenetype is not None:
        sql_3 = sql_3 + " and a.fscenetype= :fscenetype "
        param["fscenetype"] = scene.fscenetype
    sql_3 = sql_3 + " order by b.fsceneno,b.fentryid"
    data_detail = db.execute(text(sql_3), param).fetchall()

    # sTsEquipmentList 明细列表
    param = {}
    sql_2 = " select b.faction,b.factiontype,b.fentryid,b.fequipmentbarcode"
    sql_2 = sql_2 + ",b.feuipmentid,c.fdictname feuipmentname,b.finstructions,b.fposition,b.fsceneno"
    sql_2 = sql_2 + " from s_tc_scene_head a,s_tc_scene_detail b,s_ts_equipment_dict c "
    sql_2 = sql_2 + " where a.fsceneno=b.fsceneno "
    sql_2 = sql_2 + " and b.feuipmentid=c.fdictno"
    if scene.fsiteNo is not None:
        sql_2 = sql_2 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    if scene.fscenetype is not None:
        sql_2 = sql_2 + " and a.fscenetype= :fscenetype "
        param["fscenetype"] = scene.fscenetype
    sql_2 = sql_2 + " order by b.fsceneno,b.fentryid"
    data_detail2 = db.execute(text(sql_2), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['executed'] = "执行中"
        d1['fairitem'] = data[key][0]
        d1['fexectime'] = data[key][1]
        d1['fexecweek1'] = data[key][2]
        d1['fexecweek2'] = data[key][3]
        d1['fexecweek3'] = data[key][4]
        d1['fexecweek4'] = data[key][5]
        d1['fexecweek5'] = data[key][6]
        d1['fexecweek6'] = data[key][7]
        d1['fexecweek7'] = data[key][8]
        d1['foperator'] = data[key][9]
        d1['frange'] = data[key][10]
        d1['fscenename'] = data[key][11]
        d1['fsceneno'] = data[key][12]
        d1['fscenetype'] = data[key][13]
        d1['fsiteNo'] = data[key][14]
        d1['fteacherno'] = data[key][15]
        d1['fupddate'] = data[key][16]
        d1['fvalue'] = data[key][17]
        d1['fvalue2'] = data[key][18]
        d1['fwhere'] = data[key][19]
        d1['fwhere2'] = data[key][20]
        d1['fpointName'] = None
        d1['fpointNo'] = None
        d1['isExecuted'] = ""
        sbillno = data[key][12]

        weekList = list()
        if data[key][2] == "1":
            weekList.append("1")
        if data[key][3] == "1":
            weekList.append("2")
        if data[key][4] == "1":
            weekList.append("3")
        if data[key][5] == "1":
            weekList.append("4")
        if data[key][6] == "1":
            weekList.append("5")
        if data[key][7] == "1":
            weekList.append("6")
        if data[key][8] == "1":
            weekList.append("7")
        # print(weekList)
        d1['fexecweekList'] = weekList

        atsPoints = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][1]) == str(sbillno)):
                d2 = {}
                d2['fentryid'] = data_detail[j1][0]
                d2['fsceneno'] = data_detail[j1][1]
                d2['fpointNo'] = data_detail[j1][2]
                d2['fpointName'] = data_detail[j1][3]
                atsPoints.append(d2)

        sTsEquipmentList = []
        for j2 in range(len(data_detail2)):
            if (str(data_detail2[j2][8]) == str(sbillno)):
                d3 = {}
                d3['faction'] = data_detail2[j2][0]
                d3['factiontype'] = data_detail2[j2][1]
                d3['fentryid'] = data_detail2[j2][2]
                d3['fequipmentbarcode'] = data_detail2[j2][3]
                d3['fequipmenttag'] = None
                d3['feuipmentid'] = data_detail2[j2][4]
                d3['feuipmentname'] = data_detail2[j2][5]
                d3['fimage'] = None
                d3['finstructions'] = data_detail2[j2][6]
                d3['fposition'] = data_detail2[j2][7]
                d3['fsceneno'] = data_detail2[j2][8]
                sTsEquipmentList.append(d3)

        d1["atsPoints"] = atsPoints
        d1["sTsEquipmentList"] = sTsEquipmentList
        sTcSceneItemList = []
        d1["sTcSceneItemList"] = sTcSceneItemList
        sTsEquipmentList2 = []
        d1["sTsEquipmentList2"] = sTsEquipmentList2
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": scene.limit,
                 "page": scene.pageIndex,
                 "pages": (count + scene.limit - 1) // scene.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class SceneHeadCreate(BaseModel):
    """定时控制主表创建模型"""
    fscenename: str = Field(..., description="场景名称")
    fsiteNo: str = Field(..., description="站点编号")
    fexectime: str = Field(..., description="执行时间（格式：HH:MM:SS）")
    fscenetype: int = Field(..., description="场景类型（1:定时，2:周期）")
    frange: int = Field(..., description="执行范围（分钟）")
    fexecweekList: List[str] = Field(..., description="周执行列表（7位字符串，每位0/1）")

    # 自动填充字段
    fsceneno: Optional[int] = Field(None, description="场景单号（自动生成）")
    foperator: Optional[str] = Field(None, description="操作人ID")
    fupddate: datetime = Field(datetime.now(), description="更新时间")


class SceneDetailCreate(BaseModel):
    """场景设备明细模型"""
    feuipmentid: int = Field(..., description="设备ID")
    faction: str = Field(..., description="操作（如：开启、关闭）")
    finstructions: str = Field(..., description="指令")


class ScenePointCreate(BaseModel):
    """场景点位模型"""
    fpoint_no: str = Field(..., description="点位编号")


class SceneCreate(SceneHeadCreate):
    """定时控制完整创建模型（包含子表数据）"""
    sTsEquipmentList: List[SceneDetailCreate] = Field(..., description="设备列表")
    atsPoints: List[ScenePointCreate] = Field(..., description="点位列表")
    fsceneno: Optional[int] = Field(None, description="id,修改时填入")


@scenario_router.post("/create", summary="新增定时设计")
def create_scene(scene: SceneCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            # 先取单号
            fsceneno = 0
            sql = "select nextval('s_tc_scene_head_fsceneno_seq') as last_value "
            res = db.execute(text(sql)).fetchall()
            fsceneno = res[0][0]

            # 返回内容
            d1 = {}

            fexectime = scene.fexectime
            time_obj = datetime.strptime(fexectime, '%Y-%m-%d %H:%M:%S')
            fexecweekList = scene.fexecweekList
            fexecweek1 = "0"
            fexecweek2 = "0"
            fexecweek3 = "0"
            fexecweek4 = "0"
            fexecweek5 = "0"
            fexecweek6 = "0"
            fexecweek7 = "0"
            i = 0
            while i < len(fexecweekList):
                if (fexecweekList[i] == "1"):
                    fexecweek1 = "1"
                if (fexecweekList[i] == "2"):
                    fexecweek2 = "1"
                if (fexecweekList[i] == "3"):
                    fexecweek3 = "1"
                if (fexecweekList[i] == "4"):
                    fexecweek4 = "1"
                if (fexecweekList[i] == "5"):
                    fexecweek5 = "1"
                if (fexecweekList[i] == "6"):
                    fexecweek6 = "1"
                if (fexecweekList[i] == "7"):
                    fexecweek7 = "1"
                i += 1
            sql = "insert into s_tc_scene_head(fsceneno,fscenename,fsite_no,fexectime,fexecweek1,fexecweek2,fexecweek3,fexecweek4,fexecweek5,fexecweek6,fexecweek7,fscenetype,foperator,fupddate,frange,fteacherno)"
            sql = sql + " values( :fsceneno , :fscenename , :fsiteNo , :time_obj , :fexecweek1 , :fexecweek2 , :fexecweek3 , :fexecweek4 , :fexecweek5 , :fexecweek6 , :fexecweek7 , :fscenetype , :foperator ,now(), :frange , :foperator );"
            db.execute(text(sql), {
                "fsceneno": fsceneno,
                "fscenename": scene.fscenename,
                "fsiteNo": scene.fsiteNo,
                "time_obj": time_obj,
                "fexecweek1": fexecweek1,
                "fexecweek2": fexecweek2,
                "fexecweek3": fexecweek3,
                "fexecweek4": fexecweek4,
                "fexecweek5": fexecweek5,
                "fexecweek6": fexecweek6,
                "fexecweek7": fexecweek7,
                "fscenetype": scene.fscenetype,
                "foperator": scene.foperator,
                "frange": scene.frange,
            })
            # 设备列表
            sTsEquipmentList = scene.sTsEquipmentList
            for sts in sTsEquipmentList:
                sql = "insert into s_tc_scene_detail(fsceneno,feuipmentid,faction,finstructions)"
                sql = sql + " values( :fsceneno , :feuipmentid , :faction , :finstructions );"
                db.execute(text(sql), {
                    "fsceneno": fsceneno,
                    "feuipmentid": sts.feuipmentid,
                    "faction": sts.faction,
                    "finstructions": sts.finstructions,
                })

            # 教室列表
            Points = scene.atsPoints

            for point in Points:
                sql = "insert into s_tc_scene_point(fsceneno,fpoint_no)"
                sql = sql + " values( :fsceneno , :fpointNo );"
                db.execute(text(sql), {
                    "fsceneno": fsceneno,
                    "fpointNo": point.fpoint_no,
                })

            # 返回
            d1["executed"] = "执行中"
            d1["fairitem"] = None
            d1["fexectime"] = fexectime
            d1["fexecweek1"] = fexecweek1
            d1["fexecweek2"] = fexecweek2
            d1["fexecweek3"] = fexecweek3
            d1["fexecweek4"] = fexecweek4
            d1["fexecweek5"] = fexecweek5
            d1["fexecweek6"] = fexecweek6
            d1["fexecweek7"] = fexecweek7
            d1["fexecweekList"] = fexecweekList
            d1["foperator"] = scene.foperator
            d1["fpointName"] = None
            d1["fpointNo"] = None
            d1["frange"] = scene.frange
            d1["fscenename"] = scene.fscenename
            d1["fsceneno"] = fsceneno
            d1["fscenetype"] = scene.fscenetype
            d1["fsiteNo"] = scene.fsiteNo
            d1["fteacherno"] = scene.foperator
            d1["fvalue"] = 0
            d1["fvalue2"] = 0
            d1["fwhere"] = None
            d1["fwhere2"] = None
            d1["isExecuted"] = None
            d1["atsPoints"] = Points
            d1["sTsEquipmentList"] = sTsEquipmentList
            sTcSceneItemList = []
            d1["sTcSceneItemList"] = sTcSceneItemList
            sTsEquipmentList2 = []
            d1["sTsEquipmentList2"] = sTsEquipmentList2
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@scenario_router.post("/update", summary="修改定时控制")
def update_scene(scene: SceneCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            time_obj = "15 13"
            fexecweekList = scene.fexecweekList
            fexecweek1 = "0"
            fexecweek2 = "0"
            fexecweek3 = "0"
            fexecweek4 = "0"
            fexecweek5 = "0"
            fexecweek6 = "0"
            fexecweek7 = "0"
            i = 0
            while i < len(fexecweekList):
                if (fexecweekList[i] == "1"):
                    fexecweek1 = "1"
                if (fexecweekList[i] == "2"):
                    fexecweek2 = "1"
                if (fexecweekList[i] == "3"):
                    fexecweek3 = "1"
                if (fexecweekList[i] == "4"):
                    fexecweek4 = "1"
                if (fexecweekList[i] == "5"):
                    fexecweek5 = "1"
                if (fexecweekList[i] == "6"):
                    fexecweek6 = "1"
                if (fexecweekList[i] == "7"):
                    fexecweek7 = "1"
                i += 1
            sql = "update s_tc_scene_head set fscenename= :fscenename "
            sql = sql + ",fteacherno= :foperator ,fexectime= :time_obj ,frange= :frange "
            sql = sql + ",fexecweek1= :fexecweek1 ,fexecweek2= :fexecweek2 ,fexecweek3= :fexecweek3 "
            sql = sql + ",fexecweek4= :fexecweek4 ,fexecweek5= :fexecweek5 ,fexecweek6= :fexecweek6 "
            sql = sql + ",fexecweek7= :fexecweek7 "
            sql = sql + ",foperator= :foperator ,fupddate=now()"
            sql = sql + " where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fscenename": scene.fscenename,
                "foperator": user_id,
                "time_obj": time_obj,
                "frange": scene.frange,
                "fexecweek1": fexecweek1,
                "fexecweek2": fexecweek2,
                "fexecweek3": fexecweek3,
                "fexecweek4": fexecweek4,
                "fexecweek5": fexecweek5,
                "fexecweek6": fexecweek6,
                "fexecweek7": fexecweek7,
                "fsceneno": scene.fsceneno
            })
            # 设备列表
            sql = "delete from s_tc_scene_detail where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": scene.fsceneno,
            })
            sTsEquipmentList = scene.sTsEquipmentList
            for sts in sTsEquipmentList:
                sql = "insert into s_tc_scene_detail(fsceneno,feuipmentid,faction,finstructions)"
                sql = sql + " values( :fsceneno , :feuipmentid , :faction , :finstructions );"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "feuipmentid": sts.feuipmentid,
                    "faction": sts.faction,
                    "finstructions": sts.finstructions,
                })
            # 教室列表
            sql = "delete from s_tc_scene_point where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": scene.fsceneno,
            })
            Points = scene.atsPoints
            for point in Points:
                sql = "insert into s_tc_scene_point(fsceneno,fpoint_no)"
                sql = sql + " values( :fsceneno , :fpointNo );"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "fpointNo": point.fpoint_no
                })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@scenario_router.post("/delete/{fsceneno}", summary="删除定时控制")
def delete_scene(fsceneno: str = Path(description="编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from s_tc_scene_head where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno,
            })
            sql = "delete from s_tc_scene_detail where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno,
            })
            sql = "delete from s_tc_scene_point where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
